package com.example.demo1.service;

import lombok.RequiredArgsConstructor;
import com.example.demo1.entity.Score;
import com.example.demo1.entity.User;
import com.example.demo1.repository.ScoreRepository;
import com.example.demo1.repository.UserRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;

import java.util.*;
import java.util.stream.Collectors;
import lombok.Data;
import lombok.AllArgsConstructor;
import com.example.demo1.model.ImportResult;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

/**
 * 管理员服务类
 * 处理所有管理员相关的业务逻辑，包括：
 * - 成绩管理
 * - 班级分析
 * - Excel导入导出
 */
@Service
@RequiredArgsConstructor
public class AdminService {

    private final UserRepository userRepository;
    private final ScoreRepository scoreRepository;

    /**
     * 获取班级成绩并计算平均分
     * @param major 专业
     * @param className 班级名称
     * @return 包含成绩和平均分的Map
     */
    public Map<String, Object> getClassScores(String major, String className) {
        // 获取班级所有学生
        List<User> students = userRepository.findByMajorAndClassName(major, className);
        if (students.isEmpty()) {
            throw new RuntimeException("未找到该班级的学生");
        }

        // 获取学生ID列表
        List<Long> studentIds = students.stream()
                .map(User::getId)
                .collect(Collectors.toList());

        // 获取最新一次考试成绩
        List<Score> latestScores = scoreRepository.findLatestScoresByStudentIds(studentIds);
        
        // 按总分降序排序
        latestScores.sort((a, b) -> b.getTotalScore().compareTo(a.getTotalScore()));

        // 计算平均分
        Map<String, BigDecimal> averages = calculateAverages(latestScores);

        Map<String, Object> result = new HashMap<>();
        result.put("scores", latestScores);
        result.put("averages", averages);
        return result;
    }

    /**
     * 分析班级成绩趋势
     * @param major 专业
     * @param className 班级名称
     * @return 按日期分组的分析结果
     */
    public Map<String, Object> getClassAnalysis(String major, String className) {
        // 获取班级所有学生
        List<User> students = userRepository.findByMajorAndClassName(major, className);
        List<Long> studentIds = students.stream()
                .map(User::getId)
                .collect(Collectors.toList());

        // 获取所有考试成绩（按日期排序）
        List<Score> allScores = scoreRepository.findByStudentIdInOrderByExamDate(studentIds);

        // 按考试日期分组计算平均分
        Map<String, List<BigDecimal>> averagesByDate = new HashMap<>();
        allScores.stream()
                .collect(Collectors.groupingBy(Score::getExamDate))
                .forEach((date, scores) -> {
                    Map<String, BigDecimal> avgScores = calculateAverages(scores);
                    averagesByDate.put(date.toString(), Arrays.asList(
                            avgScores.get("chineseAvg"),
                            avgScores.get("mathAvg"),
                            avgScores.get("englishAvg"),
                            avgScores.get("totalAvg")
                    ));
                });

        return new HashMap<>(averagesByDate);
    }

    /**
     * 提交单个学生成绩
     * @param score 成绩信息
     */
    @Transactional
    public void submitScore(Score score) {
        // 验证学生是否存在
        User student = userRepository.findById(score.getStudentId())
                .orElseThrow(() -> new RuntimeException("学生不存在或输入信息有误"));

        // 设置学生信息
        score.setStudentName(student.getUsername());
        score.setMajor(student.getMajor());
        score.setClassName(student.getClassName());

        // 计算总分
        score.setTotalScore(score.getChineseScore()
                .add(score.getMathScore())
                .add(score.getEnglishScore()));

        scoreRepository.save(score);
    }

    /**
     * 计算平均分
     * @param scores 成绩列表
     * @return 包含各科平均分的Map
     */
    private Map<String, BigDecimal> calculateAverages(List<Score> scores) {
        Map<String, BigDecimal> averages = new HashMap<>();
        
        if (scores.isEmpty()) {
            return averages;
        }

        // 初始化总分变量
        BigDecimal totalChinese = BigDecimal.ZERO;
        BigDecimal totalMath = BigDecimal.ZERO;
        BigDecimal totalEnglish = BigDecimal.ZERO;
        BigDecimal totalScore = BigDecimal.ZERO;

        // 计算总分
        for (Score score : scores) {
            totalChinese = totalChinese.add(score.getChineseScore());
            totalMath = totalMath.add(score.getMathScore());
            totalEnglish = totalEnglish.add(score.getEnglishScore());
            totalScore = totalScore.add(score.getTotalScore());
        }

        // 计算平均分（保留两位小数）
        int count = scores.size();
        averages.put("chineseAvg", totalChinese.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));
        averages.put("mathAvg", totalMath.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));
        averages.put("englishAvg", totalEnglish.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));
        averages.put("totalAvg", totalScore.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));

        return averages;
    }

    /**
     * 获取所有有考试记录的年份
     * @return 年份列表
     */
    public List<Integer> getExamYears() {
        return scoreRepository.findDistinctYears();
    }
    
    /**
     * 获取指定年份中有考试记录的月份
     * @param year 年份
     * @return 月份列表
     */
    public List<Integer> getExamMonths(int year) {
        return scoreRepository.findDistinctMonthsByYear(year);
    }
    
    /**
     * 获取指定月份的班级成绩
     * @param major 专业
     * @param className 班级名称
     * @param year 年份
     * @param month 月份
     * @return 包含成绩和平均分的Map
     */
    public Map<String, Object> getClassScoresByDate(String major, String className, int year, int month) {
        // 获取班级所有学生
        List<User> students = userRepository.findByMajorAndClassName(major, className);
        if (students.isEmpty()) {
            throw new RuntimeException("未找到该班级的学生");
        }

        // 获取学生ID列表
        List<Long> studentIds = students.stream()
                .map(User::getId)
                .collect(Collectors.toList());

        // 获取指定月份的成绩
        List<Score> scores = scoreRepository.findByMajorAndClassNameAndYearAndMonth(
                major, className, year, month);
        
        if (scores.isEmpty()) {
            throw new RuntimeException("未找到指定时间段的考试");
        }

        // 创建用户ID到用户信息的映射
        Map<Long, User> userMap = students.stream()
                .collect(Collectors.toMap(User::getId, user -> user));

        // 更新成绩中的学生信息
        scores.forEach(score -> {
            User student = userMap.get(score.getStudentId());
            if (student != null) {
                score.setStudentId(student.getId());
                score.setStudentName(student.getUsername());
                score.setMajor(student.getMajor());
                score.setClassName(student.getClassName());
            }
        });

        // 按总分降序排序
        scores.sort((a, b) -> b.getTotalScore().compareTo(a.getTotalScore()));

        Map<String, BigDecimal> averages = calculateAverages(scores);

        Map<String, Object> result = new HashMap<>();
        result.put("scores", scores);
        result.put("averages", averages);
        return result;
    }

    /**
     * 从Excel文件导入成绩
     * @param file 上传的Excel文件
     * @return 导入结果，包含成功数量和错误信息
     * @throws IOException 文件读取失败时抛出异常
     */
    public ImportResult importScoresFromExcel(MultipartFile file) throws IOException {
        ImportResult result = new ImportResult();
        
        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);
            result.setTotal(sheet.getPhysicalNumberOfRows() - 1); // 减去表头
            
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过表头
                
                try {
                    // 数据验证
                    if (row.getCell(0) == null || row.getCell(1) == null || 
                        row.getCell(2) == null || row.getCell(3) == null || 
                        row.getCell(4) == null) {
                        throw new RuntimeException("存在空单元格");
                    }
                    
                    Score score = new Score();
                    
                    // 验证学生ID
                    Long studentId = Double.valueOf(row.getCell(0).getNumericCellValue()).longValue();
                    if (!userRepository.existsById(studentId)) {
                        throw new RuntimeException("学生不存在或输入信息有误");
                    }
                    score.setStudentId(studentId);
                    
                    // 验证成绩范围
                    double chinese = row.getCell(1).getNumericCellValue();
                    double math = row.getCell(2).getNumericCellValue();
                    double english = row.getCell(3).getNumericCellValue();
                    
                    if (chinese < 0 || chinese > 100 || math < 0 || math > 100 || 
                        english < 0 || english > 100) {
                        throw new RuntimeException("成绩必须在0-100之间");
                    }
                    
                    score.setChineseScore(BigDecimal.valueOf(chinese));
                    score.setMathScore(BigDecimal.valueOf(math));
                    score.setEnglishScore(BigDecimal.valueOf(english));
                    
                    // 验证日期格式
                    Cell dateCell = row.getCell(4);
                    LocalDate examDate;
                    
                    if (dateCell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(dateCell)) {
                        // 如果单元格是日期格式
                        examDate = dateCell.getLocalDateTimeCellValue().toLocalDate();
                    } else {
                        // 如果是字符串格式
                        String dateStr = dateCell.toString();
                        try {
                            examDate = LocalDate.parse(dateStr);
                        } catch (Exception e) {
                            throw new RuntimeException("日期格式错误，应为YYYY-MM-DD");
                        }
                    }
                    score.setExamDate(examDate);
                    
                    // 保存成绩
                    submitScore(score);
                    result.setSuccess(result.getSuccess() + 1);
                    
                } catch (Exception e) {
                    result.getErrors().add(String.format("第%d行: %s", row.getRowNum() + 1, e.getMessage()));
                }
            }
        }
        
        return result;
    }

    /**
     * 生成成绩导入模板
     * @return 包含Excel文件内容的字节数组
     * @throws IOException 文件创建失败时抛出异常
     */
    public byte[] generateExcelTemplate() throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            XSSFSheet sheet = workbook.createSheet("成绩导入模板");
            
            // 创建字体
            XSSFFont font = workbook.createFont();
            font.setFontName("Arial");
            font.setFontHeightInPoints((short) 11);
            
            // 创建单元格样式
            XSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFont(font);
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headerStyle.setBorderTop(BorderStyle.THIN);
            headerStyle.setBorderBottom(BorderStyle.THIN);
            headerStyle.setBorderLeft(BorderStyle.THIN);
            headerStyle.setBorderRight(BorderStyle.THIN);
            
            // 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {
                "Student ID", 
                "Chinese Score", 
                "Math Score", 
                "English Score", 
                "Exam Date(YYYY-MM-DD)"
            };
            
            // 设置表头内容和样式
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
                sheet.setColumnWidth(i, 20 * 256);
            }
            
            // 添加示例数据行
            Row exampleRow = sheet.createRow(1);
            CellStyle dataStyle = workbook.createCellStyle();
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            
            // 设置示例数据行样式
            for (int i = 0; i < 5; i++) {
                Cell cell = exampleRow.createCell(i);
                cell.setCellStyle(dataStyle);
            }
            
            // 写入文件
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }
} 